import time
from datetime import datetime

from app.models.data_base_models import *
from app.services import get_base_session as get_session
from app.utils.common_func_defs import *

'''—————————————————————————————方法：根据上传内容按stattime刷新数据—————————————————————————————'''
def upload_nostattime_insert(upload_df: pd.DataFrame, table_zh_name, class_name):
    # 得到处理后的上传表（中文列名转英文列名）
    df=upload_field_corr(upload_df, table_zh_name)

    #连接数据库
    session = get_session()

    # 检测想插入的数据的stat_time是否在数据库里面有东西，有就删了,由此达到刷新数据的效果
    df['stat_time'] = df['stat_time'].astype(str)
    stat_time_ls = list(df["stat_time"].drop_duplicates())
    session.query(class_name).filter(class_name.stat_time.in_(stat_time_ls)).delete()
    # 将数据按stat_time排序从早到晚排序
    df = df.sort_values(by='stat_time')
    # 添加上传该条数据记录的时间和插入该条数据记录的时间列
    df["upload_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
    df["create_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
    # 转换数据为字典
    df = df.to_dict(orient='records')
    # 批量插入
    objects = [class_name(**eachline) for eachline in df]
    session.add_all(objects)
    session.commit()
    # 关闭会话
    session.close()

'''—————————————————————————————方法：表全量更新—————————————————————————————'''
def upload_allupdate_insert(upload_df: pd.DataFrame,table_zh_name, class_name):
    # 得到处理后的上传表（中文列名转英文列名）
    df = upload_field_corr(upload_df, table_zh_name)

    #连接数据库
    session = get_session()

    # 添加上传该条数据记录的时间和插入该条数据记录的时间列
    df["upload_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
    df["create_time"] = (datetime.now()).strftime("%Y-%m-%d %H:%M:%S")
    # 转换数据为字典
    df = df.to_dict(orient='records')

    # 删除原有数据
    session.query(class_name).delete()

    # 批量插入
    objects = [class_name(**eachline) for eachline in df]
    session.add_all(objects)
    print('开始commit')
    start_time = time.time()  # 记录程序开始运行时间
    session.commit()
    end_time = time.time()  # 记录程序结束运行时间
    print('此次commit ：cost %f second' % (end_time - start_time))

    # 关闭会话
    print('commit结束')
    session.close()


'''—————————————————————————————方法：删除df列名的空格—————————————————————————————'''
def delete_col_name_blank(df0: pd.DataFrame, col_name_selected: list):
    df0.rename(columns=lambda x: x.replace(' ', ''), inplace=True)
    df0 = df0[col_name_selected]
    return df0

'''—————————————————————————————方法：上传品相、计划等联合表格—————————————————————————————'''
def pinxiang_jihua_upload(df):
    df_customer_info = df['客户信息表'] #产品明细上传表
    col_name_selected = ['客户编号', '店铺', '性质']

    df_customer_info = delete_col_name_blank(df_customer_info, col_name_selected)
    # 客户信息表
    table_zh_name = '客户信息表'
    df_customer_info = df_customer_info.astype(object).where(pd.notnull(df_customer_info), None)
    upload_allupdate_insert(df_customer_info, table_zh_name, customer_information)

    # 产品信息表
    df_product_information = df['产品信息表'] #产品明细上传表
    col_name_selected = ['物料编码', '品牌', '大系列', '子系列']

    df_product_information = delete_col_name_blank(df_product_information, col_name_selected)
    table_zh_name = '产品信息表'
    df_product_information = df_product_information.astype(object).where(pd.notnull(df_product_information), None)
    upload_allupdate_insert(df_product_information, table_zh_name, product_information)

    # 计划月表
    df_plan_month = df['计划月表'] #产品明细上传表
    df_plan_month.drop('序号', axis=1, inplace=True)
    df_plan_month = df_plan_month.melt(id_vars=['客户编码', '客户名称', '店铺', '平台', '发货\nOR实销', '品牌'], var_name='stat_time',
                    value_name='plan_amount')
    df_plan_month = df_plan_month.rename(columns={'店铺': '店铺名称',
                        '发货\nOR实销': '性质',
                        'stat_time': '数据统计时间',
                        'plan_amount': '计划发货量'})  # 更名

    col_name_selected = ['数据统计时间', '客户编码', '店铺名称', '平台', '性质', '品牌', '计划发货量']

    df_plan_month = delete_col_name_blank(df_plan_month, col_name_selected)
    table_zh_name = '计划月表'
    df_plan_month = df_plan_month.astype(object).where(pd.notnull(df_plan_month), None)
    upload_nostattime_insert(df_plan_month, table_zh_name, plan_month)

    # 发货销售拆包月表
    df_deliver_sale_bag_month = df['发货销售拆包月表'] #产品明细上传表
    col_name_selected = ['数据统计时间', '物料编码', '客户编号', '本期发货金额', '开单价金额']

    df_deliver_sale_bag_month = delete_col_name_blank(df_deliver_sale_bag_month, col_name_selected)
    table_zh_name = '发货销售拆包月表'
    df_deliver_sale_bag_month = df_deliver_sale_bag_month.astype(object).where(pd.notnull(df_deliver_sale_bag_month), None)
    upload_nostattime_insert(df_deliver_sale_bag_month, table_zh_name, deliver_sale_bag_month)

    # 发货销售月表
    df_deliver_sale_month = df['发货销售月表'] #产品明细上传表
    col_name_selected = ['数据统计时间', '物料编码', '客户编号', '本期发货金额', 'A价金额']

    df_deliver_sale_month = delete_col_name_blank(df_deliver_sale_month, col_name_selected)
    table_zh_name = '发货销售月表'
    df_deliver_sale_month = df_deliver_sale_month.astype(object).where(pd.notnull(df_deliver_sale_month), None)
    upload_nostattime_insert(df_deliver_sale_month, table_zh_name, deliver_sale_month)
